﻿namespace QLPhongKham.Access.BenhNhan
{
    using ConfigDatabase.Class;
    using Npgsql;
    using QLPhongKham.Access;
    using QLPhongKham.BaseClass.BenhNhan;
    using QLPhongKham.BaseClass.Chung;
    using QLPhongKham.BaseClass.Duoc;
    using QLPhongKham.BaseClass.VienPhi;
    using System;
    using System.Data;

    public class BNTiepDon
    {
        private ConnectDatabase _condata;
        private NpgsqlConnection _conn;
        private General _xuli;

        public BNTiepDon()
        {
            this._condata = new ConnectDatabase();
            this._xuli = new General(this._condata);
        }

        public BNTiepDon(ConnectDatabase condata)
        {
            this._condata = condata;
            this._xuli = new General(this._condata);
        }

        public string f_get_capMabn()
        {
            long num = 0L;
            try
            {
                num = long.Parse(this._xuli.f_Get_CapID(dtoBNThongTinBN.tableName, dtoBNThongTinBN.columnMABN));
                string s = DateTime.Now.ToString("yyMM") + "000001";
                if (long.Parse(s) >= num)
                {
                    num = long.Parse(s);
                }
            }
            catch
            {
            }
            return num.ToString();
        }

        public long f_insert_tiepdon(dtoBNThongTinBN ThongTinBN, dtoBNTiepDon TiepDon, dtoBNThongTinBN_LienHe LienHe)
        {
            long pIDQL;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            NpgsqlTransaction transaction = this._conn.BeginTransaction();
            try
            {
                if (!ThongTinBN.Update(this._conn) && !ThongTinBN.Insert(this._conn))
                {
                    transaction.Rollback();
                    return 0L;
                }
                TiepDon.pIDQL = long.Parse(this._xuli.f_Get_CapID());
                if (!TiepDon.Insert(this._conn))
                {
                    transaction.Rollback();
                    return 0L;
                }
                DataRow[] rowArray = new BNDanhMuc(this._condata).f_loaddata_bn_dmphong(true).Select("id=" + TiepDon.pIDPHONG.ToString());
                if ((rowArray.Length > 0) && (rowArray[0]["idvp"].ToString() != "0"))
                {
                    dtoBNChiDinh dinh = new dtoBNChiDinh(this._condata);
                    long num = long.Parse(this._xuli.f_Get_CapID(dtoBNChiDinh.tableName, dtoBNChiDinh.columnID));
                    dinh.pDONGIA = decimal.Parse(rowArray[0]["dongia"].ToString());
                    dinh.pID = num;
                    dinh.pIDDOITUONG = TiepDon.pIDDOITUONG;
                    dinh.pIDPHONG = TiepDon.pIDPHONG;
                    dinh.pIDQL = TiepDon.pIDQL;
                    dinh.pIDVP = decimal.Parse(rowArray[0]["idvp"].ToString());
                    dinh.pMABN = ThongTinBN.MABN;
                    dinh.pNGAY = TiepDon.pNGAY;
                    dinh.pSOLUONG = 1;
                    dinh.pIDDANGNHAP = TiepDon.pIDDANGNHAP;
                    if (!dinh.Insert(this._conn))
                    {
                        transaction.Rollback();
                        return 0L;
                    }
                }
                LienHe.IDQL = TiepDon.pIDQL;
                if (!LienHe.Insert(this._conn))
                {
                    transaction.Rollback();
                    return 0L;
                }
                transaction.Commit();
                pIDQL = TiepDon.pIDQL;
            }
            catch
            {
                transaction.Rollback();
                pIDQL = 0L;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return pIDQL;
        }

        public DataTable f_loaddata_bntiepdon(long idql)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = string.Concat(new object[] { 
                "select a.", dtoBNTiepDon.columnIDQL, " as idql,a.", dtoBNTiepDon.columnIDDOITUONG, " as iddoituong,to_char(", dtoBNTiepDon.columnNGAY, ",'dd/mm/yyyy hh24:mi') as ngay,a.", dtoBNTiepDon.columnIDPHONG, " as idphong,a.", dtoBNTiepDon.columnMABN, " as mabn,b.", dtoBNThongTinBN.columnHOTEN, " as hoten,b2.", dtoDMDangNhap.columnTENNGUOIDUNG, " as tennguoinhap from ", str, 
                dtoBNTiepDon.tableName, " a inner join ", str, dtoBNThongTinBN.tableName, " b on a.", dtoBNTiepDon.columnMABN, "=b.", dtoBNThongTinBN.columnMABN, " left join ", str, dtoDMDangNhap.tableName, " b2 on a.", dtoBNTiepDon.columnIDDANGNHAP, "=b2.", dtoDMDangNhap.columnID, " where a.", 
                dtoBNTiepDon.columnIDQL, "=", idql, " order by a.", dtoBNTiepDon.columnNGAY, " desc"
             });
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_caclandk(long idql)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = string.Concat(new object[] { 
                "select a.", dtoBNTiepDon.columnIDQL, " as idql,a.", dtoBNTiepDon.columnMABN, " as mabn,to_char(", dtoBNTiepDon.columnNGAY, ",'dd/mm/yyyy hh24:mi') as ngay,", dtoBNTiepDon.columnIDDOITUONG, " as iddoituong,", dtoBNTiepDon.columnIDPHONG, " as idphong,", dtoBNTiepDon.columnCANNANG, " as cannang,", dtoBNTiepDon.columnHUYETAP, " as huyetap,", dtoBNTiepDon.columnLYDOKHAM, 
                " as lydokham,", dtoBNTiepDon.columnMACH, " as mach,", dtoBNTiepDon.columnNHIETDO, " as nhietdo,", dtoBNTiepDon.columnSTT, " as stt,b.", dtoBNDMDoiTuong.columnTEN, " as tenphong,c.", dtoBNDMDoiTuong.columnTEN, " as tendoituong,a1.", dtoBNThongTinBN_LienHe.columnQUANHE, " as lhquanhe,a1.", dtoBNThongTinBN_LienHe.columnHOTEN, " as lhhoten,a1.", dtoBNThongTinBN_LienHe.columnDIACHI, 
                " as lhdiachi,a1.", dtoBNThongTinBN_LienHe.columnDIENTHOAI, " as lhdienthoai from ", str, dtoBNTiepDon.tableName, " a inner join ", str, dtoBNDMPhong.tableName, " b on a.", dtoBNTiepDon.columnIDPHONG, "=b.", dtoBNDMPhong.columnID, " inner join ", str, dtoBNDMDoiTuong.tableName, " c on a.", 
                dtoBNTiepDon.columnIDDOITUONG, "=c.", dtoBNDMDoiTuong.columnID, " left join ", str, dtoBNThongTinBN_LienHe.tableName, " a1 on a.", dtoBNTiepDon.columnIDQL, "=a1.", dtoBNThongTinBN_LienHe.columnIDQL, " where a.idql=", idql, " order by a.", dtoBNTiepDon.columnNGAY, " desc"
             });
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_caclandk(string mabn)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = "select a." + dtoBNTiepDon.columnIDQL + " as idql,a." + dtoBNTiepDon.columnMABN + " as mabn,to_char(" + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy hh24:mi') as ngay," + dtoBNTiepDon.columnIDDOITUONG + " as iddoituong," + dtoBNTiepDon.columnIDPHONG + " as idphong," + dtoBNTiepDon.columnCANNANG + " as cannang," + dtoBNTiepDon.columnHUYETAP + " as huyetap," + dtoBNTiepDon.columnLYDOKHAM + " as lydokham," + dtoBNTiepDon.columnMACH + " as mach," + dtoBNTiepDon.columnNHIETDO + " as nhietdo," + dtoBNTiepDon.columnSTT + " as stt,b." + dtoBNDMDoiTuong.columnTEN + " as tenphong,c." + dtoBNDMDoiTuong.columnTEN + " as tendoituong,a1." + dtoBNThongTinBN_LienHe.columnQUANHE + " as lhquanhe,a1." + dtoBNThongTinBN_LienHe.columnHOTEN + " as lhhoten,a1." + dtoBNThongTinBN_LienHe.columnDIACHI + " as lhdiachi,a1." + dtoBNThongTinBN_LienHe.columnDIENTHOAI + " as lhdienthoai from " + str + dtoBNTiepDon.tableName + " a left join " + str + dtoBNDMPhong.tableName + " b on a." + dtoBNTiepDon.columnIDPHONG + "=b." + dtoBNDMPhong.columnID + " left join " + str + dtoBNDMDoiTuong.tableName + " c on a." + dtoBNTiepDon.columnIDDOITUONG + "=c." + dtoBNDMDoiTuong.columnID + " left join " + str + dtoBNThongTinBN_LienHe.tableName + " a1 on a." + dtoBNTiepDon.columnIDQL + "=a1." + dtoBNThongTinBN_LienHe.columnIDQL + " where a.mabn='" + mabn + "' order by a." + dtoBNTiepDon.columnNGAY + " desc";
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_dsbnchokham(string tungay, string denngay, string mabn)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = "select a." + dtoBNTiepDon.columnIDQL + " as idql,a." + dtoBNTiepDon.columnMABN + " as mabn,to_char(" + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy hh24:mi') as ngay,a." + dtoBNTiepDon.columnIDDOITUONG + " as iddoituong,a." + dtoBNTiepDon.columnIDPHONG + " as idphong," + dtoBNTiepDon.columnCANNANG + " as cannang," + dtoBNTiepDon.columnHUYETAP + " as huyetap," + dtoBNTiepDon.columnLYDOKHAM + " as lydokham," + dtoBNTiepDon.columnMACH + " as mach," + dtoBNTiepDon.columnNHIETDO + " as nhietdo," + dtoBNTiepDon.columnSTT + " as stt,b." + dtoBNDMDoiTuong.columnTEN + " as tenphong,c." + dtoBNDMDoiTuong.columnTEN + " as tendoituong,case when a2." + dtoBNBenhAnKB.columnIDQL + " is null then 0 else 1 end as dakham,case when a1." + dtoBNThongTinBN.columnIDGIOITINH + " =0 then 'Nam' else 'Nữ' end as tengioitinh,a1." + dtoBNThongTinBN.columnNAMSINH + " as namsinh,a1." + dtoBNThongTinBN.columnDIACHI + " as diachi,a1." + dtoBNThongTinBN.columnHOTEN + " as hoten from " + str + dtoBNTiepDon.tableName + " a left join " + str + dtoBNDMPhong.tableName + " b on a." + dtoBNTiepDon.columnIDPHONG + "=b." + dtoBNDMPhong.columnID + " left join " + str + dtoBNDMDoiTuong.tableName + " c on a." + dtoBNTiepDon.columnIDDOITUONG + "=c." + dtoBNDMDoiTuong.columnID + " inner join " + str + dtoBNThongTinBN.tableName + " a1 on a." + dtoBNTiepDon.columnMABN + "=a1." + dtoBNThongTinBN.columnMABN + " left join " + str + dtoBNBenhAnKB.tableName + " a2 on a." + dtoBNTiepDon.columnIDQL + "=a2." + dtoBNBenhAnKB.columnIDQL + " where 1=1 and a2." + dtoBNBenhAnKB.columnIDQL + " is null " + ((tungay == "") ? "" : (" and to_timestamp(to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + tungay + "','dd/mm/yyyy')")) + ((denngay == "") ? "" : (" and to_timestamp(to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')<=to_timestamp('" + denngay + "','dd/mm/yyyy')")) + ((mabn == "") ? "" : (" and a." + dtoBNTiepDon.columnMABN + "='" + mabn + "'")) + " order by a." + dtoBNTiepDon.columnNGAY + "," + dtoBNTiepDon.columnSTT + ",a." + dtoBNTiepDon.columnIDQL + " asc";
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_dsbnchothanhtoan(string tungay, string denngay, string mabn)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = "select distinct a." + dtoBNTiepDon.columnIDQL + " as idql,a." + dtoBNTiepDon.columnMABN + " as mabn,to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy hh24:mi') as ngay,a." + dtoBNTiepDon.columnIDDOITUONG + " as iddoituong,a." + dtoBNTiepDon.columnIDPHONG + " as idphong," + dtoBNTiepDon.columnSTT + " as stt,b." + dtoBNDMDoiTuong.columnTEN + " as tenphong,c." + dtoBNDMDoiTuong.columnTEN + " as tendoituong,case when a2." + dtoBNBenhAnKB.columnIDQL + " is null then 0 else 1 end as dakham,case when a1." + dtoBNThongTinBN.columnIDGIOITINH + " =0 then 'Nam' else 'Nữ' end as tengioitinh,a1." + dtoBNThongTinBN.columnNAMSINH + " as namsinh,a1." + dtoBNThongTinBN.columnDIACHI + " as diachi,a1." + dtoBNThongTinBN.columnHOTEN + " as hoten from " + str + dtoBNTiepDon.tableName + " a left join " + str + dtoBNDMPhong.tableName + " b on a." + dtoBNTiepDon.columnIDPHONG + "=b." + dtoBNDMPhong.columnID + " left join " + str + dtoBNDMDoiTuong.tableName + " c on a." + dtoBNTiepDon.columnIDDOITUONG + "=c." + dtoBNDMDoiTuong.columnID + " left join " + str + dtoBNThongTinBN.tableName + " a1 on a." + dtoBNTiepDon.columnMABN + "=a1." + dtoBNThongTinBN.columnMABN + " inner join " + str + dtoBNChiDinh.tableName + " a2 on a." + dtoBNChiDinh.columnIDQL + "=a2." + dtoBNTiepDon.columnIDQL + " where 1=1 and a2." + dtoBNChiDinh.columnSLDATHANHTOAN + "<a2." + dtoBNChiDinh.columnSOLUONG + ((tungay == "") ? "" : (" and to_timestamp(to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + tungay + "','dd/mm/yyyy')")) + ((denngay == "") ? "" : (" and to_timestamp(to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')<=to_timestamp('" + denngay + "','dd/mm/yyyy')")) + ((mabn == "") ? "" : (" and a." + dtoBNTiepDon.columnMABN + "='" + mabn + "'")) + " union all select distinct a." + dtoBNTiepDon.columnIDQL + " as idql,a." + dtoBNTiepDon.columnMABN + " as mabn,to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy hh24:mi') as ngay,a." + dtoBNTiepDon.columnIDDOITUONG + " as iddoituong,a." + dtoBNTiepDon.columnIDPHONG + " as idphong," + dtoBNTiepDon.columnSTT + " as stt,b." + dtoBNDMDoiTuong.columnTEN + " as tenphong,c." + dtoBNDMDoiTuong.columnTEN + " as tendoituong,case when a2." + dtoBNBenhAnKB.columnIDQL + " is null then 0 else 1 end as dakham,case when a1." + dtoBNThongTinBN.columnIDGIOITINH + " =0 then 'Nam' else 'Nữ' end as tengioitinh,a1." + dtoBNThongTinBN.columnNAMSINH + " as namsinh,a1." + dtoBNThongTinBN.columnDIACHI + " as diachi,a1." + dtoBNThongTinBN.columnHOTEN + " as hoten from " + str + dtoBNTiepDon.tableName + " a left join " + str + dtoBNDMPhong.tableName + " b on a." + dtoBNTiepDon.columnIDPHONG + "=b." + dtoBNDMPhong.columnID + " left join " + str + dtoBNDMDoiTuong.tableName + " c on a." + dtoBNTiepDon.columnIDDOITUONG + "=c." + dtoBNDMDoiTuong.columnID + " left join " + str + dtoBNThongTinBN.tableName + " a1 on a." + dtoBNTiepDon.columnMABN + "=a1." + dtoBNThongTinBN.columnMABN + " inner join " + str + dtoDToaThuoc.tableName + " a2 on a2." + dtoDToaThuoc.columnIDQL + "=a." + dtoBNTiepDon.columnIDQL + " inner join " + str + dtoDToaThuocCT.tableName + " a21 on a21." + dtoDToaThuocCT.columnIDTOATHUOC + "=a2." + dtoDToaThuoc.columnID + " where 1=1 and a21." + dtoDToaThuocCT.columnSLDATHANHTOAN + "<a21." + dtoDToaThuocCT.columnSOLUONG + ((tungay == "") ? "" : (" and to_timestamp(to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + tungay + "','dd/mm/yyyy')")) + ((denngay == "") ? "" : (" and to_timestamp(to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')<=to_timestamp('" + denngay + "','dd/mm/yyyy')")) + ((mabn == "") ? "" : (" and a." + dtoBNTiepDon.columnMABN + "='" + mabn + "'"));
            sql = "select distinct * from (" + sql + ") a  order by ngay,stt,idql asc";
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_dsbnchothanhtoanct(string tungay, string denngay, string mabn, long idql)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = "select  a." + dtoBNTiepDon.columnIDQL + " as  idql,a." + dtoBNTiepDon.columnMABN + " as mabn,to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy hh24:mi') as ngay,a." + dtoBNTiepDon.columnIDDOITUONG + " as iddoituong,a." + dtoBNTiepDon.columnIDPHONG + " as idphong,a." + dtoBNTiepDon.columnSTT + " as stt,b." + dtoBNDMDoiTuong.columnTEN + " as tenphong,c." + dtoBNDMDoiTuong.columnTEN + " as tendoituong,case when a1." + dtoBNThongTinBN.columnIDGIOITINH + " =0 then 'Nam' else 'Nữ' end as tengioitinh,a1." + dtoBNThongTinBN.columnIDGIOITINH + " as idgioitinh,a1." + dtoBNThongTinBN.columnNAMSINH + " as namsinh,a1." + dtoBNThongTinBN.columnDIACHI + " as diachi,a1." + dtoBNThongTinBN.columnHOTEN + " as hoten,0 as thuoc,a21." + dtovpDMVienPhi.columnID + " as idvp,a21." + dtovpDMVienPhi.columnMA + " as mavp,a21." + dtovpDMVienPhi.columnTEN + " as tenvp,a21." + dtovpDMVienPhi.columnDVT + " as dvt,a21." + dtovpDMVienPhi.columnDONGIA + " as dongia,a2." + dtoBNChiDinh.columnSOLUONG + "-a2." + dtoBNChiDinh.columnSLDATHANHTOAN + " as soluong,(a2." + dtoBNChiDinh.columnSOLUONG + "-a2." + dtoBNChiDinh.columnSLDATHANHTOAN + ")*a21." + dtovpDMVienPhi.columnDONGIA + " as thanhtien,(a2." + dtoBNChiDinh.columnSOLUONG + "-a2." + dtoBNChiDinh.columnSLDATHANHTOAN + ")*a21." + dtovpDMVienPhi.columnDONGIA + " as thucthu,a2." + dtoBNChiDinh.columnID + " as idchidinh from " + str + dtoBNTiepDon.tableName + " a left join " + str + dtoBNDMPhong.tableName + " b on a." + dtoBNTiepDon.columnIDPHONG + "=b." + dtoBNDMPhong.columnID + " left join " + str + dtoBNDMDoiTuong.tableName + " c on a." + dtoBNTiepDon.columnIDDOITUONG + "=c." + dtoBNDMDoiTuong.columnID + " left join " + str + dtoBNThongTinBN.tableName + " a1 on a." + dtoBNTiepDon.columnMABN + "=a1." + dtoBNThongTinBN.columnMABN + " inner join " + str + dtoBNChiDinh.tableName + " a2 on a." + dtoBNChiDinh.columnIDQL + "=a2." + dtoBNTiepDon.columnIDQL + " inner join " + str + dtovpDMVienPhi.tableName + " a21 on a21." + dtovpDMVienPhi.columnID + "=a2." + dtoBNChiDinh.columnIDVP + " where 1=1 and a2." + dtoBNChiDinh.columnSLDATHANHTOAN + "<a2." + dtoBNChiDinh.columnSOLUONG + ((tungay == "") ? "" : (" and to_timestamp(to_char(a2." + dtoBNChiDinh.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + tungay + "','dd/mm/yyyy')")) + ((denngay == "") ? "" : (" and to_timestamp(to_char(a2." + dtoBNChiDinh.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')<=to_timestamp('" + denngay + "','dd/mm/yyyy')")) + ((mabn == "") ? "" : (" and a." + dtoBNTiepDon.columnMABN + "='" + mabn + "'")) + ((idql == 0L) ? "" : string.Concat(new object[] { " and a2.", dtoBNChiDinh.columnIDQL, "=", idql })) + " union all select  a." + dtoBNTiepDon.columnIDQL + " as idql,a." + dtoBNTiepDon.columnMABN + " as mabn,to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy hh24:mi') as ngay,a." + dtoBNTiepDon.columnIDDOITUONG + " as iddoituong,a." + dtoBNTiepDon.columnIDPHONG + " as idphong,a." + dtoBNTiepDon.columnSTT + " as stt,b." + dtoBNDMDoiTuong.columnTEN + " as tenphong,c." + dtoBNDMDoiTuong.columnTEN + " as tendoituong,case when a1." + dtoBNThongTinBN.columnIDGIOITINH + " =0 then 'Nam' else 'Nữ' end as tengioitinh,a1." + dtoBNThongTinBN.columnIDGIOITINH + " as idgioitinh,a1." + dtoBNThongTinBN.columnNAMSINH + " as namsinh,a1." + dtoBNThongTinBN.columnDIACHI + " as diachi,a1." + dtoBNThongTinBN.columnHOTEN + " as hoten,1 as thuoc,a211." + dtoDDMDuocPham.columnID + " as idvp,a211." + dtoDDMDuocPham.columnMA + " as mavp,a211." + dtoDDMDuocPham.columnTEN + " as tenvp,a211." + dtoDDMDuocPham.columnDVT + " as dvt,a21." + dtoDToaThuocCT.columnDONGIA + " as dongia,a21." + dtoDToaThuocCT.columnSOLUONG + "-a21." + dtoDToaThuocCT.columnSLDATHANHTOAN + " as soluong,(a21." + dtoDToaThuocCT.columnSOLUONG + "-a21." + dtoDToaThuocCT.columnSLDATHANHTOAN + ")*a21." + dtoDToaThuocCT.columnDONGIA + " as thanhtien,(a21." + dtoDToaThuocCT.columnSOLUONG + "-a21." + dtoDToaThuocCT.columnSLDATHANHTOAN + ")*a21." + dtoDToaThuocCT.columnDONGIA + " as thucthu,a21." + dtoDToaThuocCT.columnID + " as idchidinh from " + str + dtoBNTiepDon.tableName + " a left join " + str + dtoBNDMPhong.tableName + " b on a." + dtoBNTiepDon.columnIDPHONG + "=b." + dtoBNDMPhong.columnID + " left join " + str + dtoBNDMDoiTuong.tableName + " c on a." + dtoBNTiepDon.columnIDDOITUONG + "=c." + dtoBNDMDoiTuong.columnID + " left join " + str + dtoBNThongTinBN.tableName + " a1 on a." + dtoBNTiepDon.columnMABN + "=a1." + dtoBNThongTinBN.columnMABN + " inner join " + str + dtoDToaThuoc.tableName + " a2 on a2." + dtoDToaThuoc.columnIDQL + "=a." + dtoBNTiepDon.columnIDQL + " inner join " + str + dtoDToaThuocCT.tableName + " a21 on a21." + dtoDToaThuocCT.columnIDTOATHUOC + "=a2." + dtoDToaThuoc.columnID + " inner join " + str + dtoDDMDuocPham.tableName + " a211 on a21." + dtoDToaThuocCT.columnIDDP + "=a211." + dtoDDMDuocPham.columnID + " where 1=1 and a21." + dtoDToaThuocCT.columnSLDATHANHTOAN + "<a21." + dtoDToaThuocCT.columnSOLUONG + ((tungay == "") ? "" : (" and to_timestamp(to_char(a2." + dtoDToaThuoc.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + tungay + "','dd/mm/yyyy')")) + ((denngay == "") ? "" : (" and to_timestamp(to_char(a2." + dtoDToaThuoc.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')<=to_timestamp('" + denngay + "','dd/mm/yyyy')")) + ((mabn == "") ? "" : (" and a." + dtoBNTiepDon.columnMABN + "='" + mabn + "'")) + ((idql == 0L) ? "" : string.Concat(new object[] { " and a2.", dtoDToaThuoc.columnIDQL, "=", idql }));
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_dsbntiepdon(string tungay, string denngay)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = string.Concat(new object[] { 
                "select a.", dtoBNTiepDon.columnIDQL, " as idql,a.", dtoBNTiepDon.columnMABN, " as mabn,to_char(", dtoBNTiepDon.columnNGAY, ",'dd/mm/yyyy hh24:mi') as ngay,a.", dtoBNTiepDon.columnIDDOITUONG, " as iddoituong,a.", dtoBNTiepDon.columnIDPHONG, " as idphong,", dtoBNTiepDon.columnCANNANG, " as cannang,", dtoBNTiepDon.columnHUYETAP, " as huyetap,", dtoBNTiepDon.columnLYDOKHAM, 
                " as lydokham,", dtoBNTiepDon.columnMACH, " as mach,", dtoBNTiepDon.columnNHIETDO, " as nhietdo,", dtoBNTiepDon.columnSTT, " as stt,b.", dtoBNDMDoiTuong.columnTEN, " as tenphong,c.", dtoBNDMDoiTuong.columnTEN, " as tendoituong,case when a2.", dtoBNBenhAnKB.columnIDQL, " is null then 0 else 1 end as dakham,case when a1.", dtoBNThongTinBN.columnIDGIOITINH, " =", HangSo.enGioiTinh.Nam.GetHashCode(), 
                " then 'Nam' else 'Nữ' end as tengioitinh,a1.", dtoBNThongTinBN.columnNAMSINH, " as namsinh,a1.", dtoBNThongTinBN.columnDIACHI, "||','||a12.", dtoDMPhuongXa.columnTEN, "||','||a13.", dtoDMQuanHuyen.columnTEN, "||','||a14.", dtoDMTinhThanh.columnTEN, " as diachi,a1.", dtoBNThongTinBN.columnHOTEN, " as hoten from ", str, dtoBNTiepDon.tableName, " a left join ", 
                str, dtoBNDMPhong.tableName, " b on a.", dtoBNTiepDon.columnIDPHONG, "=b.", dtoBNDMPhong.columnID, " left join ", str, dtoBNDMDoiTuong.tableName, " c on a.", dtoBNTiepDon.columnIDDOITUONG, "=c.", dtoBNDMDoiTuong.columnID, " inner join ", str, dtoBNThongTinBN.tableName, 
                " a1 on a.", dtoBNTiepDon.columnMABN, "=a1.", dtoBNThongTinBN.columnMABN, " left join ", str, dtoBNBenhAnKB.tableName, " a2 on a.", dtoBNTiepDon.columnIDQL, "=a2.", dtoBNBenhAnKB.columnIDQL, " left join ", str, dtoDMPhuongXa.tableName, " a12 on a1.", dtoBNThongTinBN.columnIDPHUONGXA, 
                "=a12.", dtoDMPhuongXa.columnID, " left join ", str, dtoDMQuanHuyen.tableName, " a13 on a12.", dtoDMPhuongXa.columnIDQH, "=a13.", dtoDMQuanHuyen.columnID, " left join ", str, dtoDMTinhThanh.tableName, " a14 on a13.", dtoDMQuanHuyen.columnIDTT, "=a14.", dtoDMTinhThanh.columnID, 
                " where 1=1 ", (tungay == "") ? "" : (" and to_timestamp(to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + tungay + "','dd/mm/yyyy')"), (denngay == "") ? "" : (" and to_timestamp(to_char(a." + dtoBNTiepDon.columnNGAY + ",'dd/mm/yyyy'),'dd/mm/yyyy')<=to_timestamp('" + denngay + "','dd/mm/yyyy')"), " order by a.", dtoBNTiepDon.columnNGAY, ",", dtoBNTiepDon.columnSTT, ",a.", dtoBNTiepDon.columnIDQL, " asc"
             });
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DateTime f_tinh_ngaytuoi(string ngaysinh, int namsinh, int tuoi, int loaituoi)
        {
            DateTime now = DateTime.Now;
            if (ngaysinh != "")
            {
                try
                {
                    return new DateTime(int.Parse(ngaysinh.Substring(6, 4)), int.Parse(ngaysinh.Substring(3, 2)), int.Parse(ngaysinh.Substring(0, 2)));
                }
                catch
                {
                }
            }
            if (namsinh != 0)
            {
                return Convert.ToDateTime("01/01/" + namsinh.ToString());
            }
            if (tuoi != 0)
            {
                now = DateTime.Now.AddYears(-tuoi);
                if (loaituoi == HangSo.LoaiTuoi.pTuoi)
                {
                    return DateTime.Now.AddYears(-tuoi);
                }
                if (loaituoi == HangSo.LoaiTuoi.pThang)
                {
                    now = DateTime.Now.AddMonths(-tuoi);
                }
                else if (loaituoi == HangSo.LoaiTuoi.pNgay)
                {
                    now = DateTime.Now.AddDays((double) -tuoi);
                }
            }
            return now;
        }

        public decimal f_update_tiepdon(dtoBNTiepDon TiepDon, dtoBNThongTinBN_LienHe LienHe)
        {
            decimal pIDQL;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            NpgsqlTransaction transaction = this._conn.BeginTransaction();
            try
            {
                dtoBNTiepDon don = new dtoBNTiepDon(this._condata);
                dtoBNChiDinh dinh = new dtoBNChiDinh(this._condata);
                don.pIDQL = TiepDon.pIDQL;
                don.Load();
                DataTable table = new BNDanhMuc(this._condata).f_loaddata_bn_dmphong(true);
                DataRow[] rowArray = table.Select("id=" + don.pIDPHONG.ToString());
                if ((rowArray[0]["idvp"].ToString() != "0") && (this._xuli.f_Update_dulieu(string.Concat(new object[] { "delete from ", this._condata.Schema, ".", dtoBNChiDinh.tableName, " where ", dtoBNChiDinh.columnIDVP, "=", rowArray[0]["idvp"].ToString(), " and ", dtoBNChiDinh.columnIDQL, "=", don.pIDQL, " and ", dtoBNChiDinh.columnIDPHONG, "=", don.pIDPHONG }), this._conn) == -1))
                {
                    transaction.Rollback();
                    return 0M;
                }
                if (!TiepDon.Update(this._conn))
                {
                    transaction.Rollback();
                    return 0M;
                }
                rowArray = table.Select("id=" + TiepDon.pIDPHONG.ToString());
                if (rowArray[0]["idvp"].ToString() != "0")
                {
                    dinh = new dtoBNChiDinh(this._condata);
                    long num = long.Parse(this._xuli.f_Get_CapID(dtoBNChiDinh.tableName, dtoBNChiDinh.columnID));
                    dinh.pDONGIA = decimal.Parse(rowArray[0]["dongia"].ToString());
                    dinh.pID = num;
                    dinh.pIDDOITUONG = TiepDon.pIDDOITUONG;
                    dinh.pIDPHONG = TiepDon.pIDPHONG;
                    dinh.pIDQL = TiepDon.pIDQL;
                    dinh.pIDVP = decimal.Parse(rowArray[0]["idvp"].ToString());
                    dinh.pMABN = TiepDon.pMABN;
                    dinh.pNGAY = TiepDon.pNGAY;
                    dinh.pSOLUONG = 1;
                    dinh.pIDDANGNHAP = TiepDon.pIDDANGNHAP;
                    if (!dinh.Insert(this._conn))
                    {
                        transaction.Rollback();
                        return 0M;
                    }
                }
                if (!LienHe.Update(this._conn) && !LienHe.Insert(this._conn))
                {
                    transaction.Rollback();
                    return 0M;
                }
                transaction.Commit();
                pIDQL = TiepDon.pIDQL;
            }
            catch
            {
                transaction.Rollback();
                pIDQL = 0M;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return pIDQL;
        }
    }
}

